package vuelos;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

/**
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class VentanaAdmin {
	
		//Set Look & Feel
		{
			try {
				javax.swing.UIManager.setLookAndFeel(javax.swing.UIManager.getSystemLookAndFeelClassName());
			} catch(Exception e) {
				e.printStackTrace();
			}
		}
		
		private JFrame winadmin;
		private JTextArea sqlQueryArea;
		private JLabel sqlLabel;
		private JTable describeTable;
		private JButton newSQLButton;
		private JLabel errMsg;
		private JButton closeButton;
		private JLabel queryResultLabel;
		private JScrollPane resultsPanel;
		private JScrollPane describeScrollPane;
		private JComboBox tableComboBox;
		private JButton queryButton;
		private Connection link;
		private Statement stmt;
		private JTable tabla;
		
		public VentanaAdmin( Connection c ){
			iniciar();
		}

		private void iniciar(){
			
			//FRAME
			winadmin = new JFrame();
			winadmin.getContentPane().setLayout(null);
			winadmin.setBounds(21, 1, 656, 547);
			winadmin.setTitle("EBD - Proyecto 2");
			winadmin.setVisible(true);
			
			//LABELS
			sqlLabel = new JLabel();
			winadmin.getContentPane().add(sqlLabel);
			sqlLabel.setText("MySQL Query tool");
			sqlLabel.setBounds(294, 5, 101, 14);
			
			queryResultLabel = new JLabel();
			winadmin.getContentPane().add(queryResultLabel);
			queryResultLabel.setText("Resultado de la consulta:");
			queryResultLabel.setBounds(17, 154, 191, 14);
			
			errMsg = new JLabel();
			winadmin.getContentPane().add(errMsg);
			errMsg.setBounds(294, 140, 357, 22);
			
			//AREA para las consultas sql
			sqlQueryArea = new JTextArea();
			winadmin.getContentPane().add(sqlQueryArea);
			sqlQueryArea.setBounds(294, 25, 340, 74);
			sqlQueryArea.setToolTipText("MySQL Query Tool");
			sqlQueryArea.setLineWrap(true);
			
			//BOTON CONSULTA SQL
			queryButton = new JButton();
			winadmin.getContentPane().add(queryButton);
			queryButton.setText("SQL Query");
			queryButton.setBounds(346, 105, 115, 27);
			queryButton.addActionListener( new ActionListener(){
				public void actionPerformed( ActionEvent evt ){
					queryButtonActionPerformed(evt);
				}
			});
			
			//BOTON NUEVA CONSULTA
			newSQLButton = new JButton();
			winadmin.getContentPane().add(newSQLButton);
			newSQLButton.setText("Nuevo SQL");
			newSQLButton.setBounds(467, 105, 115, 27);
			newSQLButton.addMouseListener( new MouseAdapter(){
				public void mouseClicked( MouseEvent evt ){
					newSQLButtonMouseClicked(evt);
				}
			});
			
			//TABLA DE ENTIDADES
			ComboBoxModel tableComboBoxModel = new DefaultComboBoxModel(new String[] {  });
			tableComboBox = new JComboBox();
			winadmin.getContentPane().add(tableComboBox);
			tableComboBox.setModel(tableComboBoxModel);
			tableComboBox.setBounds(12, 3, 254, 18);
			tableComboBox.addItemListener( new ItemListener(){
				public void itemStateChanged( ItemEvent evt ){
					tableComboBoxItemStateChanged(evt);
				}
			});
			
			//TABLA DE ATRIBUTOS Y TIPO
			describeScrollPane = new JScrollPane();
			winadmin.getContentPane().add(describeScrollPane);
			describeScrollPane.setBounds(12, 25, 254, 110);
			{
				TableModel describeTableModel =
					new DefaultTableModel(
					new String[][] { },
					new String[] { "Atributo","Tipo"  }
				);
				describeTable = new JTable();
				describeScrollPane.setViewportView(describeTable);
				describeTable.setModel(describeTableModel);
				describeTable.setBounds(100, 131, 248, 81);
			}
			
			//PANEL RESULTADO
			resultsPanel = new JScrollPane();
	        winadmin.getContentPane().add(resultsPanel, BorderLayout.CENTER);
	        resultsPanel.setBounds(12, 174, 622, 304);
	        {
	               tabla = new JTable();
	               BorderLayout tablaLayout = new BorderLayout();
	               tabla.setLayout(tablaLayout);
	               resultsPanel.setViewportView(tabla);
	               //tabla.setAutoCreateRowSorter(true);
	               tabla.setBounds(2, 2, 588, 246);
	               tabla.setPreferredSize(new java.awt.Dimension(616, 297));
	        }
			
			//BOTON CERRAR
	        closeButton = new JButton();
			winadmin.getContentPane().add(closeButton);
			closeButton.setText("Cerrar");
			closeButton.setBounds(512, 484, 115, 27);
			closeButton.addMouseListener(new MouseAdapter() {
				public void mouseClicked(MouseEvent evt) {
					closeButtonMouseClicked(evt);
				}
			});
			
			connect();
			loadCombobox();
		}

		private void queryButtonActionPerformed(ActionEvent evt) {
			refrescarTabla();
		}
		
		private void refrescarTabla(){
		    try{
		    	 errMsg.setText("");
		    	 String sql = sqlQueryArea.getText();
		         // se crea una sentencia o comando jdbc para realizar la consulta
		         Statement stmt = this.link.createStatement();
		         if (sql.toLowerCase().split(" ")[0].equals("select")) {

			         // se ejecuta la sentencia y se recibe un resultset
			         ResultSet rs = stmt.executeQuery(sql.trim());
			         // se adapta el modelo de la tabla según los metadatos del resultado
			         ResultSetMetaData rsmd = rs.getMetaData();
			         ConsultasTableModel modelo = new ConsultasTableModel();
			         for (int i = 1; i <= rsmd.getColumnCount(); i++)
			         {
			            String columnaNombre = rsmd.getColumnLabel(i);
			            Class columnaTipo = Class.forName(rsmd.getColumnClassName(i));
			            boolean columnaEditable = false;
			            modelo.agregarColumna(columnaNombre, columnaTipo, columnaEditable);
			         }
			         tabla.setModel(modelo);

			         // se recorre el resulset y se actualiza la tabla en pantalla
			         while (rs.next())
			         {
			            Vector<Object> fila = new Vector<Object>();
			            for (int j = 1; j <= rsmd.getColumnCount(); j++)
			            {
			               fila.add(rs.getObject(j));
			          }
			            ((ConsultasTableModel) this.tabla.getModel()).addRow(fila);
			         }

			         tabla.setVisible(true);
			         rs.close();
			         stmt.close();

		         } else {

		        	  stmt.execute(sql.trim());
		        	  errMsg.setText("OK");
		         }

		      }
		      catch (SQLException ex)
		      {
		         // en caso de error, se muestra la causa en la consola
		    	 errMsg.setText(ex.getMessage());
		    	 System.out.println("SQLException: " + ex.getMessage());
		         System.out.println("SQLState: " + ex.getSQLState());
		         System.out.println("VendorError: " + ex.getErrorCode());
		      }
		      catch (ClassNotFoundException e)
		      {
		         e.printStackTrace();
		      }
		   }
		 
		private void tableComboBoxItemStateChanged(ItemEvent evt) {
				updateList();
		}
		
		private void updateList(){
			try {
		         stmt = this.link.createStatement();
		         String table = (String) this.tableComboBox.getItemAt(this.tableComboBox.getSelectedIndex());
		         String sql = "DESCRIBE " + table;
		         ResultSet rs = stmt.executeQuery(sql);
		         ((DefaultTableModel) this.describeTable.getModel()).setRowCount(0);
		         int i = 0;
		         while (rs.next()) {
		            ((DefaultTableModel) this.describeTable.getModel()).setRowCount(i + 1);
		            this.describeTable.setValueAt(rs.getString("Field"), i, 0);
		            this.describeTable.setValueAt(rs.getString("Type"), i, 1);
		            i++;
		         }

		         rs.close();
		         stmt.close();
			} catch (SQLException ex){
		         System.out.println("SQLException: " + ex.getMessage());
		         System.out.println("SQLState: " + ex.getSQLState());
		         System.out.println("VendorError: " + ex.getErrorCode());
			}

		}
		
		private void newSQLButtonMouseClicked(MouseEvent evt) {
			sqlQueryArea.setText("");
		}
		
		private void closeButtonMouseClicked(MouseEvent evt) {
			winadmin.setVisible(false);
			winadmin.dispose();
			this.disconnect();
			new Principal();
		}
		
		private void connect(){
			if (this.link == null)
		      {
		         try
		         {
		            Class.forName("com.mysql.jdbc.Driver").newInstance();
		         }
		         catch (Exception ex)
		         {
		            System.out.println(ex.getMessage());
		         }
		         try
		         {
		        	 String servidor = "localhost:3306";
			         String baseDatos = "vuelos";
			         String usuario = "admin";
			         String clave = "admin";
			         String urlConexion = "jdbc:mysql://" + servidor + "/" + baseDatos;
			         this.link = DriverManager.getConnection(urlConexion, usuario, clave);
		         }
		         catch (SQLException ex)
		         {
		        	System.out.println("Problema de autenticación en la base de datos.");
		            System.out.println("SQLException: " + ex.getMessage());
		            System.out.println("SQLState: " + ex.getSQLState());
		            System.out.println("VendorError: " + ex.getErrorCode());
		         }
		      }
		}
		
		private void loadCombobox() {

			try
			{
			String Consulta = "SHOW TABLES";
			stmt = link.createStatement();
			ResultSet result = stmt.executeQuery(Consulta);
			while (result.next()){
				String txt = result.getString("Tables_in_vuelos");
				tableComboBox.addItem(txt);
					}
			updateList();
			stmt.close();
			}
			catch(SQLException error)
			{
				System.out.println("Error en consulta de listado de tablas.");
			}
		}
		
		private void disconnect(){
			
			if (this.link != null)
		      {
		         try{
		        	 	this.link.close();
		        	 	
		         }catch (SQLException ex){
		        	 
		            System.out.println("SQLException: " + ex.getMessage());
		            System.out.println("SQLState: " + ex.getSQLState());
		            System.out.println("VendorError: " + ex.getErrorCode());
		         }
		      }
		   }
}


